# -*- coding: utf-8 -*-

''' SqlStore/NormalStore的测试类
    
    测试原则：逐步编织和完善测试网, 拒绝一步到位，也拒绝同样错误再次漏过.
        验证主功能走通并正确
        对于边界情况和异常流程，在发生错误时定位捕获. 初步时可只针对重要情况做事先测试，也可不做。
'''


from unittest import TestCase
#from django.test import TestCase
from wolfox.foxit.dune.test_common import prepare,clean,fetch_info
from wolfox.foxit.dune.models import *
from wolfox.foxit.dune.store import *
from wolfox.foxit.base.common import Quote,XInfo,Report as TReport
from django.db import transaction
from sqlite3 import IntegrityError


PAIRS = [('SH00000A',u'上海长江'),('SH00000B',u'上海黄河'),('SH00000X',u'上海发展2'),('SH00000Y',u'上海进步')]

def prepare_xit(connection):
    '''除权数据准备'''
    cursor = connection.cursor()
    #数据准备
    #除权数据
    cursor.execute("insert into dune_powerinfo(id,tstock_id,sgbl,pgbl,fhbl,pgj,zfs,zfj,jjs,register_day,execute_day,day_state,hour_state,m5_state) \
            values(1001,1,0,0,200,0,0,0,0,20050701,20050701,0,0,0)")     #分红，1股分0.2
    cursor.execute("insert into dune_powerinfo(id,tstock_id,sgbl,pgbl,fhbl,pgj,zfs,zfj,jjs,register_day,execute_day,day_state,hour_state,m5_state) \
            values(1002,1,1000,0,0,0,0,0,0,20050702,20050702,0,0,0)")    #送股1送1
    cursor.execute("insert into dune_powerinfo(id,tstock_id,sgbl,pgbl,fhbl,pgj,zfs,zfj,jjs,register_day,execute_day,day_state,hour_state,m5_state) \
            values(1003,1,0,1000,0,10000,0,0,0,20050703,20050703,0,0,0)")    #配股,1配1,10元价格
    cursor.execute("insert into dune_powerinfo(id,tstock_id,sgbl,pgbl,fhbl,pgj,zfs,zfj,jjs,register_day,execute_day,day_state,hour_state,m5_state) \
            values(1004,1,1000,0,1000,0,0,0,0,20050704,20050704,0,0,0)")    #分红加送股, 1送1股送1元
    #行情数据
    cursor.execute("insert into dune_xdayquote(id,tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                " values(1001,1,20050601,20000,21000,22000,19000,20500,100000,2050000)")
    cursor.execute("insert into dune_xdayquote(id,tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                " values(1002,1,20050701,20000,21000,22000,19000,20500,100000,2050000)")
    cursor.execute("insert into dune_xdayquote(id,tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                " values(1003,1,20050801,20000,21000,22000,19000,20500,100000,2050000)")
    #陪衬
    cursor.execute("insert into dune_xdayquote(id,tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                " values(1004,2,20050601,20000,21000,22000,19000,20500,100000,2050000)")

class SqlStoreTest(TestCase):

    def setUp(self):
        from django.db import connection
        prepare()
        self.info = fetch_info()
        self.store = SqlStore(connection)

    def tearDown(self):
        clean()

    def testQuery(self):
        #通路测试
        from django.db import connection
        records = self.store.query(connection,"select * from dune_sdayquote")
        self.assertTrue(len(records) > 1)

    def testQuery2(self):
        #通路测试
        from django.db import connection
        records = self.store.query2(connection,"select * from dune_sdayquote")
        self.assertTrue(len(records) > 1)
        #django的定制sql只支持format方式，并且其sqlite实现只支持%s
        records2 = self.store.query2(connection,"select * from dune_sdayquote where tdate>%s and tdate<%s",0,99999998)
        self.assertTrue(len(records2) > 1)

    def testGetCode2Id(self):  #测试通透性
        cmap = self.store.get_code2id()
        self.assertTrue(len(cmap) > 0)

    def testGetId2Code(self):  #测试通透性
        imap = self.store.get_id2code()
        self.assertTrue(len(imap) > 0)

    def test_reset_code_id_mapping(self):  #测试通透性
        from django.db import connection        
        self.store.reset_code_id_mapping(connection)
        self.assertTrue(True)

    def testOrganizeTuples2Quotes(self):
        #通路测试
        rms = self.store.organize_tuples2quotes([])
        self.assertEquals([],rms.keys())
        #负荷测试
        t1 = ('SH01',20070101,1,2,3,4,5,6,7)
        t2 = ('SH01',20070102,1,2,3,4,5,6,7)
        t3 = ('SH01',20070103,1,2,3,4,5,6,7)
        t4 = ('SH02',20070101,1,2,3,4,5,6,7)
        t5 = ('SH03',20070101,1,2,3,4,5,6,7)        
        rms = self.store.organize_tuples2quotes([t1,t2,t3,t4,t5])
        self.assertEquals(3,len(rms.keys()))
        self.assertEquals(3,len(rms['SH01']))
        self.assertEquals(1,len(rms['SH02']))
        self.assertEquals(1,len(rms['SH03']))        

    def testOrganizeTuples2Quotes2(self):
        #通路测试
        rms = self.store.organize_tuples2quotes2([])
        self.assertEquals([],rms.keys())
        #负荷测试
        t1 = ('SH01',20070101,1,2,3,4,5,6,7)
        t2 = ('SH01',20070102,1,2,3,4,5,6,7)
        t3 = ('SH01',20070103,1,2,3,4,5,6,7)
        t4 = ('SH02',20070101,1,2,3,4,5,6,7)
        t5 = ('SH03',20070101,1,2,3,4,5,6,7)        
        rms = self.store.organize_tuples2quotes2([t1,t2,t3,t4,t5])
        #print '**********************************************************************'
        #print rms
        self.assertEquals(3,len(rms.keys()))
        self.assertEquals(3,len(rms['SH01']))
        self.assertEquals(1,len(rms['SH02']))
        self.assertEquals(1,len(rms['SH03']))        

    def testOrganizeTuples2Quotes2_b(self):
        #通路测试
        rms = self.store.organize_tuples2quotes2_b([])
        self.assertEquals([],rms.keys())
        #负荷测试
        t1 = ('SH01',20070101,1,2,3,4,5,6,7)
        t2 = ('SH01',20070102,1,2,3,4,5,6,7)
        t3 = ('SH01',20070103,1,2,3,4,5,6,7)
        t4 = ('SH02',20070101,1,2,3,4,5,6,7)
        t5 = ('SH03',20070101,1,2,3,4,5,6,7)        
        rms = self.store.organize_tuples2quotes2_b([t1,t2,t3,t4,t5])
        #print '**********************************************************************'
        #print rms
        self.assertEquals(3,len(rms.keys()))
        self.assertEquals(3,len(rms['SH01']))
        self.assertEquals(1,len(rms['SH02']))
        self.assertEquals(1,len(rms['SH03']))        
    
    def testOrganizeTuples2Quotes2_c(self):
        #通路测试
        rms = self.store.organize_tuples2quotes2_c([])
        self.assertEquals([],rms.keys())
        #负荷测试
        t1 = ('SH01',20070101,1,2,3,4,5,6,7)
        t2 = ('SH01',20070102,1,2,3,4,5,6,7)
        t3 = ('SH01',20070103,1,2,3,4,5,6,7)
        t4 = ('SH02',20070101,1,2,3,4,5,6,7)
        t5 = ('SH03',20070101,1,2,3,4,5,6,7)        
        rms = self.store.organize_tuples2quotes2([t1,t2,t3,t4,t5])
        #print '**********************************************************************'
        #print rms
        self.assertEquals(3,len(rms.keys()))
        self.assertEquals(3,len(rms['SH01']))
        self.assertEquals(1,len(rms['SH02']))
        self.assertEquals(1,len(rms['SH03']))        


    def testRemoveSQuotes(self):
        sname = 'SH00000X'
        sname2 = 'SH00000Y'
        sc = SDayQuote.objects.filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(2,sc)
        from django.db import connection
        self.store.remove_squotes(connection,20070101,20080101)
        #验证已经删除
        sc = SDayQuote.objects.filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(0,sc)
        #验证另一段时间没被搞定
        sc = SDayQuote.objects.filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>1)
        
    def testRemoveSQuotes2Single(self):
        from django.db import connection
        sname = 'SH00000X'
        sname2 = 'SH00000Y'
        source = self.info.fxj #分析家                
        #通道测试
        self.store.remove_squotes2(connection,[],20070101,20080101)        
        #单个测试
        sc = SDayQuote.objects.filter(sstock=sname).filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(1,sc)
        self.store.remove_squotes2(connection,[sname],20070101,20080101)
        #验证已经删除
        sc = SDayQuote.objects.filter(sstock=sname).filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(0,sc)
        #验证另一个没被搞定
        sc = SDayQuote.objects.filter(sstock=sname).filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>0)
        #验证其它股票没影响
        sc = SDayQuote.objects.filter(sstock=sname2).filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>1)

    def testRemoveSQuotes2List(self):
        from django.db import connection        
        sname = 'SH00000X'
        sname2 = 'SH00000Y'
        source = self.info.fxj #分析家
        sc = SDayQuote.objects.filter(sstock__in=(sname,sname2)).filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(2,sc)
        self.store.remove_squotes2(connection,[sname,sname2],20070101,20080101)
        #验证已经删除
        sc = SDayQuote.objects.filter(sstock__in=(sname,sname2)).filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(0,sc)
        #验证另一段时间没被搞定
        sc = SDayQuote.objects.filter(sstock__in=(sname,sname2)).filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>1)

    def testRemoveXQuotes(self):
        sc = XDayQuote.objects.filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(2,sc)
        from django.db import connection
        self.store.remove_xquotes(connection,20070101,20080101)
        #验证已经删除
        sc = XDayQuote.objects.filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(0,sc)
        #验证另一段时间没被搞定
        sc = XDayQuote.objects.filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>1)

    def testRemoveXQuotes2Single(self):
        from django.db import connection
        sc = XDayQuote.objects.filter(tstock__code='SH00000X').filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(1,sc)
        self.store.remove_xquotes2(connection,['SH00000X'],20070101,20080101)
        #验证已经删除
        sc = XDayQuote.objects.filter(tstock__code='SH00000X').filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(0,sc)
        #验证另一个没被搞定
        sc = XDayQuote.objects.filter(tstock=1).filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>0)
        #验证其它股票没影响
        sc = XDayQuote.objects.filter(tstock=2).filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>1)

    def testRemoveXQuotes2List(self):
        from django.db import connection        
        sc = XDayQuote.objects.filter(tstock__code__in=('SH00000X','SH00000Y')).filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(2,sc)
        self.store.remove_xquotes2(connection,['SH00000X','SH00000Y'],20070101,20080101)
        #验证已经删除
        sc = XDayQuote.objects.filter(tstock__code__in=('SH00000X','SH00000Y')).filter(tdate__range=(20070101,20080101)).count()
        self.assertEquals(0,sc)
        #验证另一段时间没被搞定
        sc = XDayQuote.objects.filter(tstock__code__in=('SH00000X','SH00000Y')).filter(tdate__range=(0,99999999)).count()
        self.assertTrue(sc>1)

    def testGetLatestSquoteDay(self):
        from django.db import connection
        last_day = self.store.get_latest_squote_day(connection)
        self.assertTrue(last_day > 19900101)

    def testGetLatestXquoteDay(self):
        from django.db import connection
        last_day = self.store.get_latest_xquote_day(connection)
        self.assertTrue(last_day > 19900101)

    def testGetLatestHourSquoteDay(self):
        from django.db import connection
        last_day = self.store.get_latest_hour_squote_day(connection)
        self.assertTrue(True)

    def testGetLatestHourXquoteDay(self):
        from django.db import connection
        last_day = self.store.get_latest_hour_xquote_day(connection)
        self.assertTrue(True)

    def testGetLatestXquote_close(self):
        from django.db import connection
        last_close = self.store.get_latest_xquote_close(connection,1,20080702)
        self.assertEquals(12500,last_close)
        last_close = self.store.get_latest_xquote_close(connection,30,10090101)        
        self.assertEquals(0,last_close)

    def testAddSQuotes(self):
        quote1,quote2,quote3 = Quote(),Quote(),Quote()
        quote1.tstock,quote2.tstock,quote3.tstock = 'SH00000X','SH00000X','SH00000W'
        quote1.tdate,quote2.tdate,quote3.tdate = 20080101,20080404,20080707
        quotes = [quote1,quote2,quote3]
        from django.db import connection
        oc = SDayQuote.objects.all().count()
        self.store.add_squotes(connection,quotes)
        nc = SDayQuote.objects.all().count()
        self.assertEquals(oc+3,nc)
        #验证重复插入所引发的完整性错误
        self.assertRaises(IntegrityError,self.store.add_squotes,connection,quotes) 
 
    def testAddHoursSQuotes(self):
        quote1,quote2,quote3 = Quote(),Quote(),Quote()
        quote1.tstock,quote2.tstock,quote3.tstock = 'SH00000X','SH00000X','SH00000W'
        quote1.tdate,quote2.tdate,quote3.tdate = 2008010101,2008040401,2008070701
        quotes = [quote1,quote2,quote3]
        from django.db import connection
        oc = SHourQuote.objects.all().count()
        self.store.add_hour_squotes(connection,quotes)
        nc = SHourQuote.objects.all().count()
        self.assertEquals(oc+3,nc)
        #验证重复插入所引发的完整性错误
        self.assertRaises(IntegrityError,self.store.add_hour_squotes,connection,quotes) 

    def testAddSQuotesTuple(self):
        quote1,quote2,quote3 = Quote(),Quote(),Quote()
        quote1.tstock,quote2.tstock,quote3.tstock = 'SH00000X','SH00000X','SH00000W'
        quote1.tdate,quote2.tdate,quote3.tdate = 20080101,20080404,20080707
        quotes = [quote1,quote2,quote3]
        from django.db import connection
        oc = SDayQuote.objects.all().count()
        self.store.add_squotes_tuple(connection,quotes)
        nc = SDayQuote.objects.all().count()
        self.assertEquals(oc+3,nc)
        #验证重复插入所引发的完整性错误
        self.assertRaises(IntegrityError,self.store.add_squotes_tuple,connection,quotes) 
    
    def testTransferXQuotes(self):
        from django.db import connection
        #清理环境
        cursor = connection.cursor()
        cursor.execute('delete from dune_sdayquote')
        cursor.execute('delete from dune_xdayquote')
        #空转测试
        self.store.transfer_xquotes(connection,0,0)
        #测试倒过的数据中，1.没有跨越时间的数据 2.没有跨越证券类型的数据. 因此在测试用例中需要有这类数据
        quote1,quote2,quote3,quote4,quote5 = Quote(),Quote(),Quote(),Quote(),Quote()
        quote1.tstock,quote2.tstock,quote3.tstock,quote4.tstock,quote5.tstock = 'SH00000X','SH00000X','SH00000X','SH00000Y','SH00000Z'
        quote1.tdate,quote2.tdate,quote3.tdate,quote4.tdate,quote5.tdate = 20070101,20070404,20080707,20070101,20071010
        quotes = [quote1,quote2,quote3,quote4,quote5]
        self.store.add_squotes(connection,quotes)    #如果该函数出问题，则这个测试也会有问题
        self.store.transfer_xquotes(connection,20070101,20080101)
        nc = XDayQuote.objects.all().count()
        self.assertEquals(3,nc) #增加了3条记录.
        #确认这三条记录
        x1 = XDayQuote.objects.get(tstock=1,tdate=20070101)
        x2 = XDayQuote.objects.get(tstock=1,tdate=20070404)
        x3 = XDayQuote.objects.get(tstock=2,tdate=20070101)
        #未导入其它记录,抛出wolfox.foxit.dune.models.DoesNotExist，但无法静态导入
        self.assertRaises(Exception,XDayQuote.objects.get,tstock=1,tdate=20080707)
        #验证重复转换所引发的完整性错误
        self.assertRaises(IntegrityError,self.store.transfer_xquotes,connection,20070101,20080101)

    def testTransferXQuotes2(self):
        from django.db import connection
        #清理环境        
        cursor = connection.cursor()
        cursor.execute('delete from dune_sdayquote')
        cursor.execute('delete from dune_xdayquote')
        #空转测试
        self.store.transfer_xquotes2(connection,[],0,0)
        self.store.transfer_xquotes2(connection,['SH000001','SH000002'],0,0)
        quote1,quote2,quote3,quote4,quote5 = Quote(),Quote(),Quote(),Quote(),Quote()
        #测试倒过的数据中，1.没有跨越时间的数据 2. 没有stocks之外的数据
        quote1.tstock,quote2.tstock,quote3.tstock,quote4.tstock,quote5.tstock = 'SH00000X','SH00000X','SH00000X','SH00000Y','SH00000Z'
        quote1.tdate,quote2.tdate,quote3.tdate,quote4.tdate,quote5.tdate = 20070101,20070404,20080707,20070101,20071010
        quotes = [quote1,quote2,quote3,quote4,quote5]
        self.store.add_squotes(connection,quotes)    #如果该函数出问题，则这个测试也会有问题
        self.store.transfer_xquotes2(connection,['SH00000X','SH00000Z'],20070101,20080101)
        nc = XDayQuote.objects.all().count()
        self.assertEquals(3,nc) #增加了3条记录. 
        #确认这三条记录
        x1 = XDayQuote.objects.get(tstock=1,tdate=20070101)
        x2 = XDayQuote.objects.get(tstock=1,tdate=20070404)
        x3 = XDayQuote.objects.get(tstock=3,tdate=20071010)
        #未导入其它记录,抛出wolfox.foxit.dune.models.DoesNotExist，但无法静态导入
        self.assertRaises(Exception,XDayQuote.objects.get,tstock=2,tdate=20070101)
        #验证重复转换所引发的完整性错误
        self.assertRaises(IntegrityError,self.store.transfer_xquotes,connection,20070101,20080101)

    def testTransferHourXQuotes2(self):#通路测试
        from django.db import connection
        self.store.transfer_hour_xquotes2(connection,[])

    def testAddQuotes(self):
        quote1,quote2,quote3 = Quote(),Quote(),Quote()
        quote1.tstock,quote2.tstock,quote3.tstock = 'SH00000X','SH00000X','SH00000NOCODE'
        quote1.tdate,quote2.tdate,quote3.tdate = 20080101,20080404,20080707
        quotes = [quote1,quote2,quote3]
        from django.db import connection
        soc = SDayQuote.objects.all().count()
        xoc = XDayQuote.objects.all().count()
        self.store.add_quotes(connection,quotes)
        snc = SDayQuote.objects.all().count()
        xnc = XDayQuote.objects.all().count()
        self.assertEquals(soc+1,snc)
        self.assertEquals(xoc+2,xnc)
        #验证重复插入所引发的完整性错误
        self.assertRaises(IntegrityError,self.store.add_squotes,connection,quotes) 

    def testAddHourQuotes(self):
        #仅测试通路，其余已在testAddQuotes中测试
        from django.db import connection
        self.store.add_hour_quotes(connection,[])
        self.assertTrue(True)

    def prepare_squotes(self,connection):
        quote1,quote2,quote3 = Quote(),Quote(),Quote()
        quote1.tdate,quote2.tdate,quote3.tdate = 20080404,20080101,20080907 #日期反列，测试order_by的有效性
        quote1.tstock,quote2.tstock,quote3.tstock = 'SH00000M','SH00000M','SH00000M'
        quote1.tstock,quote2.tstock,quote3.tstock = 'SH00000N','SH00000N','SH00000N'
        self.store.add_squotes_tuple(connection,[quote1,quote2,quote3])

    def testGetSquotes(self):
        from django.db import connection
        self.prepare_squotes(connection)
        #验证source_id=1
        rms = self.store.get_squotes(connection,0,99999999)
        self.assertEquals(3,len(rms.keys()))
        self.assertEquals(None,rms.get('SH00000M'))
        self.assertTrue(len(rms.get('SH00000X'))>0)
        self.assertTrue(len(rms.get('SH00000Y'))>0)
        self.assertEquals(3,len(rms.get('SH00000N')))        
        self.assertEquals(20080101,rms['SH00000N'][0].tdate)    #有顺序
        self.assertEquals(20080404,rms['SH00000N'][1].tdate)
        self.assertEquals(20080907,rms['SH00000N'][2].tdate)        

    def testGetSquotes2(self):
        from django.db import connection
        self.prepare_squotes(connection) 
        rms = self.store.get_squotes(connection,0,99999999)        
        #验证source_id=1
        rms = self.store.get_squotes2(connection,['SH00000X','SH00000N'],0,99999999)
        self.assertEquals(2,len(rms.keys()))
        self.assertEquals(None,rms.get('SH00000M'))
        self.assertEquals(None,rms.get('SH00000Y'))        
        self.assertTrue(len(rms.get('SH00000X'))>0)
        self.assertEquals(3,len(rms.get('SH00000N')))
        #验证source_id=1的时间范围
        rms = self.store.get_squotes2(connection,['SH00000X','SH00000N'],20080101,20080715)
        self.assertEquals(2,len(rms.get('SH00000N')))
        self.assertEquals(1,len(rms.get('SH00000X')))
        self.assertEquals(None,rms.get('SH00000Y'))
        self.assertEquals(None,rms.get('SH00000M'))        
        self.assertEquals(20080101,rms['SH00000N'][0].tdate)
        self.assertEquals(20080404,rms['SH00000N'][1].tdate)
        self.assertTrue(20080101 <= rms['SH00000X'][0].tdate < 20080715)

    def prepare_xquotes(self,connection):
        cursor = connection.cursor()
        #数据准备
        cursor.execute("insert into dune_stockcode(id,code,name,corporation_id,exchange_id,stype_id,total_size,outstanding_size,last_modified,remark,is_active)" \
                    " values(11,'SH01','测试1',1001,'SHSE','STOCK',0,0,'2008-01-01','',1)")
        cursor.execute("insert into dune_stockcode(id,code,name,corporation_id,exchange_id,stype_id,total_size,outstanding_size,last_modified,remark,is_active)" \
                    " values(12,'SH02','测试2',1002,'SHSE','STOCK',0,0,'2008-01-01','',1)")
        cursor.execute("insert into dune_xdayquote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                    " values(11,'20060201',0,0,0,0,0,0,0)")
        cursor.execute("insert into dune_xdayquote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                    " values(11,'20060101',0,0,0,0,0,0,0)")
        cursor.execute("insert into dune_xdayquote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                    " values(11,'20080301',0,0,0,0,0,0,0)")
        cursor.execute("insert into dune_xdayquote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                    " values(12,'20080401',0,0,0,0,0,0,0)")
        cursor.execute("insert into dune_xdayquote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)" \
                    " values(12,'20060301',0,0,0,0,0,0,0)")
        
    def testGetXquotes(self):
        from django.db import connection
        self.prepare_xquotes(connection)
        #全时间范围
        rms = self.store.get_xquotes(connection,0,99999999)
        self.assertEquals(4,len(rms.keys()))    #X,Y,SH01,SH01
        self.assertEquals(None,rms.get('SH00000M'))
        self.assertTrue(len(rms.get('SH00000X'))>0)
        self.assertTrue(len(rms.get('SH00000Y'))>0)
        self.assertEquals(3,len(rms['SH01']))
        self.assertEquals(2,len(rms['SH02']))
        self.assertEquals(20060101,rms['SH01'][0].tdate)
        self.assertEquals(20060201,rms['SH01'][1].tdate)
        self.assertEquals(20080301,rms['SH01'][2].tdate)        
        #具体时间范围
        rms = self.store.get_xquotes(connection,20060101,20070101)
        self.assertEquals(2,len(rms.keys()))    #X,Y,SH01,SH01
        self.assertEquals(None,rms.get('SH00000M'))
        self.assertEquals(None,rms.get('SH00000X'))
        self.assertEquals(None,rms.get('SH00000Y'))        
        self.assertEquals(2,len(rms['SH01']))
        self.assertEquals(1,len(rms['SH02']))
        self.assertEquals(20060101,rms['SH01'][0].tdate)
        self.assertEquals(20060201,rms['SH01'][1].tdate)
        self.assertEquals(20060301,rms['SH02'][0].tdate)

    def testGetXquotes2(self):
        from django.db import connection
        self.prepare_xquotes(connection)
        #通路测试
        rms = self.store.get_xquotes2(connection,[],0,99999999)        
        #全时间范围
        rms = self.store.get_xquotes2(connection,['SH01','SH02'],0,99999999)
        self.assertEquals(2,len(rms.keys()))    #X,Y,SH01,SH01
        self.assertEquals(None,rms.get('SH00000M'))
        self.assertEquals(None,rms.get('SH00000X'))
        self.assertEquals(None,rms.get('SH00000Y'))        
        self.assertEquals(3,len(rms['SH01']))
        self.assertEquals(2,len(rms['SH02']))
        self.assertEquals(20060101,rms['SH01'][0].tdate)
        self.assertEquals(20060201,rms['SH01'][1].tdate)
        self.assertEquals(20080301,rms['SH01'][2].tdate)     
        #具体时间范围
        rms = self.store.get_xquotes2(connection,['SH01','SH02'],20060101,20080101) 
        self.assertEquals(2,len(rms.keys()))    #X,Y,SH01,SH01
        self.assertEquals(None,rms.get('SH00000M'))
        self.assertEquals(None,rms.get('SH00000X'))
        self.assertEquals(None,rms.get('SH00000Y'))        
        self.assertEquals(2,len(rms['SH01']))
        self.assertEquals(1,len(rms['SH02']))
        self.assertEquals(20060101,rms['SH01'][0].tdate)
        self.assertEquals(20060201,rms['SH01'][1].tdate)
        self.assertEquals(20060301,rms['SH02'][0].tdate)        

    def testRefSql(self):
        rsql = self.store.ref_sql()
        rsql2 = self.store.ref_sql('day')
        rsql3 = self.store.ref_sql('hour')
        self.assertEquals(rsql,rsql2)

    def testGetRefBasedQuotes(self):
        from django.db import connection
        self.prepare_xquotes(connection)
        #通路测试
        rms = self.store.get_refbased_xquotes(connection,1002,1003,0,99999999)
        self.assertTrue(True)

    def testGetRefBasedHourQuotes(self):
        from django.db import connection
        self.prepare_xquotes(connection)
        #通路测试
        rms = self.store.get_refbased_hour_xquotes(connection,1002,1003,0,99999999)
        self.assertTrue(True)

    def testAddStockToCatalog(self):
        from django.db import connection        
        #通路测试
        self.store.add_stock_to_catalog(connection,u'测试',u'行业',[])
        self.store.add_stock_to_catalog(connection,u'测试',u'行业',['SH00000X','SH00000Y'])
        self.assertRaises(IntegrityError,self.store.add_stock_to_catalog,connection,u'测试',u'行业',['SH00000X','SH00000Y'])
        c = Catalog.objects.get(name=u'测试')
        ss = list(c.stocks.filter(code__in = ('SH00000X','SH00000Y')).order_by('id'))
        self.assertEquals(2,len(ss))
        self.assertEquals('SH00000X',ss[0].code)
        self.assertEquals('SH00000Y',ss[1].code)        
    
    def testXitEmpty(self):
        from django.db import connection
        prepare_xit(connection)
        #空转
        self.store.xit(connection,[])
    
    def testXitFh(self):
        from django.db import connection
        prepare_xit(connection)
        #分红
        xi = PowerInfo.objects.get(id=1001)
        self.assertTrue(not xi.day_state)
        self.store.xit(connection,[xi])
        xd = XDayQuote.objects.get(id=1001)
        self.assertEquals(19810,xd.topen)
        self.assertEquals(20800,xd.tclose)
        self.assertEquals(20305,xd.tavg)
        self.assertEquals(100962,xd.tvolume)
        self.assertEquals(2050000,xd.tamount)
        xi_after = PowerInfo.objects.get(id=1001)
        self.assertTrue(xi_after.day_state)
        #以下是以分红为例对相关逻辑的验证，其它方式不再验证
        #以分红为例，验证未影响除权日之后的数据
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(20000,xd.topen)
        xd = XDayQuote.objects.get(id=1003)
        self.assertEquals(20000,xd.topen)
        #以分红为例，验证未影响其它的数据
        xd = XDayQuote.objects.get(id=1004)
        self.assertEquals(20000,xd.topen)
        #以分红为例，验证连续两次除权
        self.store.xit(connection,[xi,xi])
        xd = XDayQuote.objects.get(id=1001)
        #print '........................................;'
        self.assertEquals(19430,xd.topen)

    def testXitSg(self):
        from django.db import connection
        prepare_xit(connection)
        #送股
        xi = PowerInfo.objects.get(id=1002)
        self.store.xit(connection,[xi])
        xd = XDayQuote.objects.get(id=1001)
        self.assertEquals(10000,xd.topen)
        self.assertEquals(10250,xd.tavg)        
        self.assertEquals(200000,xd.tvolume)        
        #验证20050701的那条记录
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(10000,xd.topen)
        self.assertEquals(10250,xd.tavg)                
        self.assertEquals(200000,xd.tvolume)        

    def testXitPg(self):
        from django.db import connection
        prepare_xit(connection)
        #送股
        xi = PowerInfo.objects.get(id=1003)
        self.store.xit(connection,[xi])
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(14762,xd.topen)
        self.assertEquals(15131,xd.tavg)        
        self.assertEquals(135484,xd.tvolume)        
    
    def testXitFhSg(self):
        from django.db import connection
        prepare_xit(connection)
        #送股分红
        xi = PowerInfo.objects.get(id=1004)
        print '....................................;'
        self.store.xit(connection,[xi])
        print '....................................;'        
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(9524,xd.topen)
        self.assertEquals(9762,xd.tavg)        
        self.assertEquals(210000,xd.tvolume)        
        #验证未影响除权日之后的数据 (因为对1004除权日的笔误，将20050704误为20080704，因此设定下面的重复测试来验证不再有笔误)
        xd = XDayQuote.objects.get(id=1003)
        self.assertEquals(20000,xd.topen)
    
    def testXitHour(self):
        from django.db import connection
        prepare_xit(connection)
        #空转,仅测试其通路性，其余已在xit中测试
        self.store.xit_hour(connection,[])
        self.assertTrue(True)
    
    def testXitEmpty_old(self):
        from django.db import connection
        prepare_xit(connection)
        #空转
        self.store.xit_old(connection,[])

    def testXitFh_old(self):
        from django.db import connection
        prepare_xit(connection)
        #分红
        xi = PowerInfo.objects.get(id=1001)
        self.store.xit_old(connection,[xi])
        xd = XDayQuote.objects.get(id=1001)
        self.assertEquals(19800,xd.topen)
        self.assertEquals(20300,xd.tavg)
        self.assertEquals(100985,xd.tvolume)
        self.assertEquals(2050000,xd.tamount)
        #以下是以分红为例对相关逻辑的验证，其它方式不再验证
        #以分红为例，验证未影响除权日之后的数据
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(20000,xd.topen)
        xd = XDayQuote.objects.get(id=1003)
        self.assertEquals(20000,xd.topen)
        #以分红为例，验证未影响其它的数据
        xd = XDayQuote.objects.get(id=1004)
        self.assertEquals(20000,xd.topen)
        #以分红为例，验证连续两次除权
        self.store.xit_old(connection,[xi,xi])
        xd = XDayQuote.objects.get(id=1001)
        self.assertEquals(19400,xd.topen)

    def testXitSg_old(self):
        from django.db import connection
        prepare_xit(connection)
        #送股
        xi = PowerInfo.objects.get(id=1002)
        self.store.xit_old(connection,[xi])
        xd = XDayQuote.objects.get(id=1001)
        self.assertEquals(10000,xd.topen)
        self.assertEquals(10250,xd.tavg)        
        self.assertEquals(200000,xd.tvolume)        
        #验证20050701的那条记录
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(10000,xd.topen)
        self.assertEquals(10250,xd.tavg)                
        self.assertEquals(200000,xd.tvolume)        

    def testXitPg_old(self):
        from django.db import connection
        prepare_xit(connection)
        #送股
        xi = PowerInfo.objects.get(id=1003)
        self.store.xit_old(connection,[xi])
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(15000,xd.topen)
        self.assertEquals(15250,xd.tavg)        
        self.assertEquals(134426,xd.tvolume)        

    def testXitFhSg_old(self):
        from django.db import connection
        prepare_xit(connection)
        #送股分红
        xi = PowerInfo.objects.get(id=1004)
        self.store.xit_old(connection,[xi])
        xd = XDayQuote.objects.get(id=1002)
        self.assertEquals(9500,xd.topen)
        self.assertEquals(9750,xd.tavg)        
        self.assertEquals(210256,xd.tvolume)        
        #验证未影响除权日之后的数据 (因为对1004除权日的笔误，将20050704误为20080704，因此设定下面的重复测试来验证不再有笔误)
        xd = XDayQuote.objects.get(id=1003)
        self.assertEquals(20000,xd.topen)


class NormalStoreTest(TestCase):
    def setUp(self):
        prepare()
        self.info = fetch_info()
        self.store = NormalStore()

    def tearDown(self):
        clean()

    def testGetStockMap(self):  #测试通透性
        stockmap = self.store.get_stockmap()
        self.assertTrue(len(stockmap) > 0)

    def testAddStockToCatalog2(self):
        from django.db import connection        
        #通路测试
        self.store.add_stock_to_catalog2(u'测试',[])
        self.store.add_stock_to_catalog2(u'测试',['SH00000X','SH00000Y'])
        self.store.add_stock_to_catalog2(u'测试',['SH00000X','SH00000Y'])   #重复插入不会引起异常
        c = Catalog.objects.get(name=u'测试')
        ss = list(c.stocks.filter(code__in = ('SH00000X','SH00000Y')).order_by('id'))
        self.assertEquals(2,len(ss))
        self.assertEquals('SH00000X',ss[0].code)
        self.assertEquals('SH00000Y',ss[1].code)        

    def testAddStockToCatalog3(self):
        from django.db import connection        
        #通路测试
        self.store.add_stock_to_catalog3(u'测试',[])
        self.store.add_stock_to_catalog3(u'测试',['SH00000X','SH00000Y'])
        self.store.add_stock_to_catalog3(u'测试',['SH00000X','SH00000Y'])   #重复插入不会引起异常
        c = Catalog.objects.get(name=u'测试')
        ss = list(c.stocks.filter(code__in = ('SH00000X','SH00000Y')).order_by('id'))
        self.assertEquals(2,len(ss))
        self.assertEquals('SH00000X',ss[0].code)
        self.assertEquals('SH00000Y',ss[1].code)        

    def testUpdateCodes(self):
        shfz = StockCode.objects.get(code = PAIRS[2][0])
        #更名前验证
        self.assertEquals(u'上海发展',shfz.name)
        corp_num = Corporation.objects.count()
        codes = [ (self.info.shse,pair[0],pair[1],self.info.stype)for pair in PAIRS ]
        newcodes = self.store.update_codes(codes)
        #验证新添加的code的代码,名称和corp
        self.assertEquals(2,len(newcodes))
        self.assertEquals(PAIRS[0][0],newcodes[0])
        nc1 = StockCode.objects.get(code=newcodes[0])
        self.assertEquals(PAIRS[0][0],nc1.code)
        self.assertEquals(PAIRS[0][1],nc1.corporation.name)
        self.assertEquals(PAIRS[1][0],newcodes[1])
        nc2 = StockCode.objects.get(code=newcodes[1])
        self.assertEquals(PAIRS[1][0],nc2.code)
        self.assertEquals(PAIRS[1][1],nc2.corporation.name)
        #新添加两个公司
        self.assertEquals(corp_num + 2,Corporation.objects.count())
        #验证更名部分
        shfz = StockCode.objects.get(code = PAIRS[2][0],exchange=self.info.shse)        
        self.assertEquals(u'上海发展2',shfz.name)        
        #验证不动部分
        shjb = StockCode.objects.get(code = PAIRS[3][0],exchange=self.info.shse)        
        self.assertEquals(u'上海进步',shjb.name)        

    def testUpdateCodes2(self):
        shfz = StockCode.objects.get(code = PAIRS[2][0],exchange=self.info.shse)
        #更名前验证
        self.assertEquals(u'上海发展',shfz.name)
        corp_num = Corporation.objects.count()
        newcodes = self.store.update_codes2(PAIRS,self.info.shse,self.info.stype)
        #验证新添加的code的代码,名称和corp
        self.assertEquals(2,len(newcodes))
        self.assertEquals(PAIRS[0][0],newcodes[0][0])
        self.assertEquals(PAIRS[0][0],newcodes[0][1].code)
        self.assertEquals(PAIRS[0][1],newcodes[0][1].corporation.name)
        self.assertEquals(PAIRS[1][0],newcodes[1][0])
        self.assertEquals(PAIRS[1][0],newcodes[1][1].code)
        self.assertEquals(PAIRS[1][1],newcodes[1][1].corporation.name)
        #新添加两个公司
        self.assertEquals(corp_num + 2,Corporation.objects.count())
        #验证更名部分
        shfz = StockCode.objects.get(code = PAIRS[2][0],exchange=self.info.shse)        
        self.assertEquals(u'上海发展2',shfz.name)        
        #验证不动部分
        shjb = StockCode.objects.get(code = PAIRS[3][0],exchange=self.info.shse)        
        self.assertEquals(u'上海进步',shjb.name)        

    def testAddMapping(self):
        print 'zzzzzzzzzzzzzzzzzzzzzzzzzzz',self.info.shse,self.info.stype
        newcodes = self.store.update_codes2(PAIRS,self.info.shse,self.info.stype)
        map_num = Mapping.objects.count()
        self.store.add_mappings(newcodes,self.info.fxj)
        #仅仅验证数量
        self.assertEquals(map_num + 2,Mapping.objects.count())
 
    def testAddXInfos(self):
        #只测试通路
        #空测试
        self.store.add_xinfos([])
        #负荷测试
        x1,x2,x3 = XInfo(),XInfo(),XInfo()
        x1.tstock,x2.tstock,x3.tstock = u'SH00000X',u'SH00000X',u'SH00000Y'
        x1.annouce_date,x2.annouce_date,x3.annouce_date = 20010104,20020104,20010104
        x1.texecuteday,x2.texecuteday,x3.texecuteday = 20010104,20020104,20010104
        self.store.add_xinfos([x1])
        self.store.add_xinfos([x2,x3])
        self.assertEquals(3,PowerInfo.objects.all().count())
        #验证重复插入异常IntegrityError
        self.assertRaises(IntegrityError,self.store.add_xinfos,[x1])

    def testAddXInfosInvalid(self): #测试对非法数据(没有标准代码对应)的处理
        x1,x2,x3 = XInfo(),XInfo(),XInfo()
        x1.tstock,x2.tstock,x3.tstock = u'SH00000X',u'SH00000X',u'INVALID'
        x1.annouce_date,x2.annouce_date,x3.annouce_date = 20010104,20020104,20010104
        x1.texecuteday,x2.texecuteday,x3.texecuteday = 20010104,20020104,20010104
        self.store.add_xinfos([x3])
        self.store.add_xinfos([x2,x3])  #x3貌似插入两次，但实际上都没有插入。不出错间接说明x3没有插入过
        self.assertTrue(True)   #到此即算成功

    def testAddReport(self):
        #只测试通路
        #空测试
        self.store.add_reports([])
        #负荷测试
        x1,x2,x3 = TReport(),TReport(),TReport()
        x1.tstock,x2.tstock,x3.tstock = 'SH00000X','SH00000X','SH00000Y'
        x1.treleaseday,x2.treleaseday,x3.treleaseday = 20010104,20020104,20030104
        x1.tdate,x2.tdate,x3.tdate = 200101,200203,200301
        self.store.add_reports([x1])
        self.store.add_reports([x2,x3])
        self.assertEquals(3,Report.objects.all().count())
        #验证tperiod
        self.assertEquals(200101,Report.objects.get(announce_day=20010104).tperiod)
        #验证重复插入异常IntegrityError
        self.assertRaises(IntegrityError,self.store.add_reports,[x1])

    def testAddReportInvalid(self):
        #只测试通路
        #空测试
        self.store.add_reports([])
        #负荷测试
        x1,x2,x3 = TReport(),TReport(),TReport()
        x1.tstock,x2.tstock,x3.tstock = 'SH00000X','SH00000X','INVALID'
        x1.treleaseday,x2.treleaseday,x3.treleaseday = 20010104,20020104,20010104
        x1.tdate,x2.tdate,x3.tdate = 200101,200103,200101
        self.store.add_reports([x3])
        self.store.add_reports([x2,x3]) #x3貌似插入两次，但实际上都没有插入。不出错间接说明x3没有插入过
        self.assertTrue(True)   #到此即算成功

    def testUpdateStockSize(self):
        #只测试通路
        stock = StockCode.objects.get(code = 'SH00000X')
        updated = self.store.update_stock_size(stock,1000,100)
        self.assertTrue(updated)
        updated = self.store.update_stock_size(stock,1000,100)  #第二次同样数据就不更新了
        self.assertFalse(updated)
        updated = self.store.update_stock_size(stock,1001,100)  #不同数据继续更新
        self.assertTrue(updated)

    def testGetXInfoKeys(self):
        from django.db import connection
        prepare_xit(connection)
        keys = self.store.get_xinfo_keys(20050101,20061231)
        self.assertEquals(4,len(keys))
        self.assertEquals('SH00000X',keys[0][0])
        self.assertEquals('SH00000X',keys[1][0])
        self.assertEquals('SH00000X',keys[2][0])
        self.assertEquals('SH00000X',keys[3][0])        

    def testGetReportKeys(self):  #本测试依赖于add_reports的正确性
        from django.db import connection
        x1,x2,x3 = TReport(),TReport(),TReport()
        x1.tstock,x2.tstock,x3.tstock = 'SH00000X','SH00000Y','SH00000Z'
        x1.treleaseday,x2.treleaseday,x3.treleaseday = 20010304,20020504,20030104
        x1.tdate,x2.tdate,x3.tdate = 200101,200203,200301
        self.store.add_reports([x1,x2,x3])
        keys = self.store.get_report_keys(20000101,20030101)
        self.assertEquals(2,len(keys))
        self.assertEquals('SH00000X',keys[0][0])
        self.assertEquals('SH00000Y',keys[1][0])
        self.assertEquals(200101,keys[0][1])
        self.assertEquals(200203,keys[1][1])

    def test_gbjg(self):
        from django.db import connection
        x1,x2,x3 = TReport(),TReport(),TReport()
        x1.tstock,x2.tstock,x3.tstock = 'SH00000X','SH00000X','SH00000Z'
        x1.treleaseday,x2.treleaseday,x3.treleaseday = 20010304,20020504,20030104
        x1.tdate,x2.tdate,x3.tdate = 200103,200205,200301
        x1.tzgb,x2.tzgb,x3.tzgb = 1000,2000,3000
        x1.tag,x2.tag,x3.tag = 500,1000,2000
        self.store.add_reports([x1,x2,x3])
        dos = self.store.gbjg(connection)
        self.assertEquals(2,len(dos))
        self.assertEquals(1,dos[0].id)
        self.assertEquals(200205,dos[0].date)
        self.assertEquals(2000,dos[0].zgb)
        self.assertEquals(1000,dos[0].ag)
        self.assertEquals(3,dos[1].id)
        self.assertEquals(200301,dos[1].date)
        self.assertEquals(3000,dos[1].zgb)
        self.assertEquals(2000,dos[1].ag)        
